﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Security;
using Excel;
using Host.Helper;

namespace Host.Models
{
    /// <summary>
    /// The class that helps with the handling of the file and validations
    /// </summary>
    public class FileHelper
    {
        
        #region Constant
        /// <summary>
        /// The amount of sheets that need to be evaluated
        /// </summary>
        private const int SheetCount = 4;

        private const string SessionPerviouseSet = "PerviouseSet";
        private const string SessionHasPreviouse = "hasOld";
        
        private enum ClaimColumns
        {
            MemberNumber = 0,
            ClaimNumber = 1,
            ClaimType = 2,
            DateNotified = 3,
            ExitDate = 4,
            ClaimPaymentStatus = 5,
            TotalAmountPaid = 6,
            TotalOutstandingAmount = 7,
            LastSuccessFulPaymentData = 8
        }

        private enum MembersColumns
        {
            MemberNumber = 0,
            UniqueMemberRecordIndicator = 1,
            MemberFirstName = 2,
            MemberOtherName = 3,
            MemberSurname = 4,
            MemberInitials = 5,
            MemberStatus = 6,
            EmployerNumber = 7,
            EmployeeNumber = 8,
            IDNumber = 9,
            PassportNumber = 10,
            Gender = 11,
            DateOfBirth = 12,
            DateEmployed = 13,
            DateJoinedFund = 14,
            DateOfNotification = 15,
            ExitDate = 16,
            Language = 17,
            Salary = 18,
            IncomeAmount = 19,
            FirstContributionDate = 20,
            LastContributionDate = 21,
            MembersShare = 22,
            HomeLoadAmount = 23,
            SARSNumber = 24
        }


        /// <summary>
        /// Helper for the create and reading of the session object called 'FileLogId'
        /// </summary>
        private const string FileLog = "FileLogId";
        /// <summary>
        /// The seets that are going to the validated and processed
        /// </summary>
       // private readonly string[] _sheets = new string[] { "Claim", "Members", "Employer", "Transaction" };
        #endregion
        
        /// <summary>
        /// Save the file into the database, table used is the FileLog table in the data store
        /// </summary>
        /// <param name="model">FileModel object</param>
        /// <param name="file">byte array representng the file</param>
        /// <returns>bool, true if successful and false if failed</returns>
        public bool SaveFile(FileModel model, byte[] file)
        {
           

            HttpContext.Current.Session[SessionHasPreviouse] = CheckForPreviouseVersion(model);
            HttpContext.Current.Session["FundId"] = model.FundId;
            
            using (var db = new Data())//Create the using object to allow automatic cleaning of resources
            {
                try
                {
                    MembershipUser user = (MembershipUser)HttpContext.Current.Session["User"];//Get the user from the session object 
                    
                    var uploadUserId = new Data().aspnet_Users//Get the GUID that represents the current user
                        .Where(i => i.UserName == user.UserName)
                        .Select(x => x.UserId).SingleOrDefault();
                   
                    db.FileLog.AddObject(new FileLog //Save the file and related information into the database
                                             {
                                                 FundId = model.FundId,
                                                 FileName = model.FileName,
                                                 DateCreated = DateTime.Now,
                                                 EffectivePeriod = model.EffectivePeriod,
                                                 FileData = file,
                                                 FileProcessStatusId = "S",
                                                 CreatedBy =
                                                     user.Email,
                                                 UploadUser = uploadUserId
                                             });
                    db.SaveChanges();//Submit the changes to the database
                    return true;//Set the result to true
                }
                catch (System.Exception ex)//Catch a system exception
                {
                    ExceptionHelper.CreateException(Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, (int)ErrorHelper.ErrorTokin.FileSaveError);//Write the exception into the database
                    return false;//Set the result to false
                }
            }
        }

        /// <summary>
        /// Validate the Uploaded File
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ValidateFile(FileModel model)
        {
            #region Old Code
            //using (FileStream file = File.Open(fileLocation, FileMode.Open))//Use the filestream object to load the stream for the ExcelReader
            //{
            //    try
            //    {
            //        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(file);//Load the file into the Excel Data Reader
            //        DataSet result = excelReader.AsDataSet();//Send the results to DataSet
            //        if (result.Tables.Count < _sheets.Count())//Count the tables in the DataSet and then compare against the amount in the array of valid sheets
            //            return false;
            //        return true;
            //    }
            //    catch (System.Exception ex)
            //    {
            //        ExceptionHelper.CreateException(Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, 90);//Write the exception into the database
            //        return false;
            //    }
            //}
            #endregion

            /* Get the DataSet from the model */
            DataSet uploadedDataSet = model.Data;
            /*  */
            if(uploadedDataSet.Tables.Count < SettingsModel.Sheets.Count())
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Processes the file, by prepairing it for the rules handler, brake it to tables 
        /// and pass it to the ProcessTables method,
        /// if a error is found then will write a exception into the database
        /// </summary>
        /// <param name="model"></param>
        public void ProcessFile(FileModel model)
        {
            //Now has the uploaded Dataset as property Called Data
            try
            {
                if (ValidateFile(model))//Check if the file structure is correct
                {
                    DataSet uploadedDataSet = model.Data;
                    foreach (var sheet in SettingsModel.Sheets)
                    {
                        ProcessTable(uploadedDataSet.Tables[sheet], sheet,model);
                    }

                    #region OldCode
                    //using (FileStream file = File.Open(fileLocation, FileMode.Open))//Use the filestream object to load the stream for the ExcelReader
                    //{
                    //    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(file);//Load the file into the Excel Data Reader
                    //    DataSet result = excelReader.AsDataSet();//Send the results to DataSet
                    //    foreach (string sheetName in _sheets)//Loop through the items in the array of sheets that need to be processed
                    //    {
                    //        DataTable table = result.Tables[sheetName];//Get the DataTable
                    //        ProcessTable(table, sheetName,model );//Process the table 
                    //    }
                    //}
                    #endregion
                }
            }
            catch (System.Exception ex)
            {
                ExceptionHelper.CreateException(Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, (int)ErrorHelper.ErrorTokin.FileProcessError);//Write the exception into the database
            }
            
        }

        /// <summary>
        /// Processes the table, extract the questions from the database, 
        /// use the question get the correct answer then compare the question and answer
        /// Write exceptions into the FileExceptionLog table
        /// </summary>
        /// <param name="table">DataTable object</param>
        /// <param name="key">SheetName</param>
        /// <param name="model">FileModel object</param>
        private static void ProcessTable(DataTable table, string key, FileModel model)
        {
            DataRow dr = table.Rows[0];
            switch (key)
            {
                case "Claim":
                    #region Claim
                    #region Validate Structure
                    if (dr["MemberNumber"].ToString() != "MemberNumber")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [MemberNumber] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }

                    if (dr["ClaimNumber"].ToString() != "ClaimNumber")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [ClaimNumber] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }

                    if (dr["ClaimType"].ToString() != "ClaimType")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [ClaimType] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }

                    if (dr["DateNotified"].ToString() != "DateNotified")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [DateNotified] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    if (dr["ExitDate"].ToString() != "ExitDate")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [ExitDate] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    if (dr["ClaimPaymentStatus"].ToString() != "ClaimPaymentStatus")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [ClaimPaymentStatus] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    if (dr["TotalAmountPaid"].ToString() != "TotalAmountPaid")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [TotalAmountPaid] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    if (dr["TotalOutstandingAmount"].ToString() != "TotalOutstandingAmount")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [TotalOutstandingAmount] in" + key 
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    if (dr["LastSuccessFulPaymentDate"].ToString() != "LastSuccessFulPaymentDate")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [LastSuccessFulPaymentDate] in" + key
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }

                    #endregion

                    #region Validate Needed Data

                    for (int i = 1; i < table.Rows.Count -1; i++)
                    {
                        #region Check If Has Value
                        /* Check if the records have a Member Number */
                        if (table.Rows[i][(int)ClaimColumns.MemberNumber].ToString().Trim() == "" || table.Rows[i][(int)ClaimColumns.MemberNumber].ToString() == string.Empty)
                       {
                           ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [MemberNumber] in" + key
                                                + " has not value", (int)ErrorHelper.ErrorTokin.NoValueError);
                       }

                        /* Check if the records have a Claim Number */
                        if (table.Rows[i][(int)ClaimColumns.ClaimNumber].ToString().Trim() == "" || table.Rows[i][(int)ClaimColumns.ClaimNumber].ToString() == string.Empty)
                        {
                            ExceptionHelper.CreateException(
                                                 Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [ClaimNumber] in" + key
                                                 + " has not value", (int)ErrorHelper.ErrorTokin.NoValueError);
                        }

                        /* Check if the records have a Claim Type */
                        if (table.Rows[i][(int)ClaimColumns.ClaimType].ToString().Trim() == "" || table.Rows[i][(int)ClaimColumns.ClaimType].ToString() == string.Empty)
                        {
                            ExceptionHelper.CreateException(
                                                 Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [ClaimType] in" + key
                                                 + " has not value", (int)ErrorHelper.ErrorTokin.NoValueError);
                        }

                        /* Check if the records have a Total Amount Paid */
                        if (table.Rows[i][(int)ClaimColumns.TotalAmountPaid].ToString().Trim() == "" || table.Rows[i][(int)ClaimColumns.TotalAmountPaid].ToString() == string.Empty)
                        {
                            ExceptionHelper.CreateException(
                                                 Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [TotalAmountPaid] in" + key
                                                 + " has not value", (int)ErrorHelper.ErrorTokin.NoValueError);
                        }

                        /* Check if the records have a Total Outstanding Amount */
                        if (table.Rows[i][(int)ClaimColumns.TotalOutstandingAmount].ToString().Trim() == "" || table.Rows[i][(int)ClaimColumns.TotalOutstandingAmount].ToString() == string.Empty)
                        {
                            ExceptionHelper.CreateException(
                                                 Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [TotalOutstandingAmount] in" + key
                                                 + " has not value", (int)ErrorHelper.ErrorTokin.NoValueError);
                        }
                        #endregion

                        #region Validate Dates

                        if (table.Rows[i][(int)ClaimColumns.MemberNumber].ToString().Trim() != "" || table.Rows[i][(int)ClaimColumns.MemberNumber].ToString() != string.Empty)
                            {
                                //Get the Member from DB
                                using(Data db = new Data())
                                {
                                    MembershipUser user = (MembershipUser) HttpContext.Current.Session["User"];

                                    int rowIndicator = i;
                                    var getMember =
                                        db.Member.Where(
                                            m => m.MemberNo == table.Rows[rowIndicator][(int) ClaimColumns.MemberNumber].ToString()).Select(
                                                x => x.MemberId).SingleOrDefault();
// ReSharper disable ConditionIsAlwaysTrueOrFalse
                                    if(getMember!= null )
// ReSharper restore ConditionIsAlwaysTrueOrFalse
                                    {
                                        //Create Claim Record [MemberId, ClaimNo, ClaimTypeId, NotificationDate, 
                                        //ExitDate, ClaimPaymentsStatus, TotalPaidAmount, TotalOutStanding, LastSuccessFullPayDate, 
                                        //EffectivePeriod, Validated, IsValid, DateCreated]
                                        #region Save Record
                                        var r = table.Rows[0];
                                        MemberClaim memberClaim = new MemberClaim
                                                                      {
                                                                          ClaimNo = r[(int)ClaimColumns.ClaimNumber].ToString(),
                                                                          ClaimTypeId = r[(int)ClaimColumns.ClaimType].ToString(),
                                                                          NotificationDate = r[(int)ClaimColumns.DateNotified].ToString().Trim() == "" ? (DateTime?)null : Convert.ToDateTime(r[(int)ClaimColumns.DateNotified].ToString()),
                                                                          ExitDate = r[(int)ClaimColumns.ExitDate].ToString().Trim() == ""? (DateTime?)null : Convert.ToDateTime(r[(int)ClaimColumns.ExitDate].ToString()),
                                                                          ClaimPaymentStatusId = r[(int)ClaimColumns.ClaimPaymentStatus].ToString(),
                                                                          TotalPaidAmount = r[(int)ClaimColumns.TotalAmountPaid].ToString().Trim() == ""? (Decimal?)null : Convert.ToDecimal(r[(int)ClaimColumns.TotalAmountPaid].ToString()),
                                                                          TotalOutstandingAmount = r[(int)ClaimColumns.TotalOutstandingAmount].ToString().Trim() == "" ? (Decimal?)null : Convert.ToDecimal(r[(int)ClaimColumns.TotalOutstandingAmount].ToString()),
                                                                          LastSuccessfulPayDate = r[(int)ClaimColumns.LastSuccessFulPaymentData].ToString().Trim() == ""? (DateTime?)null: Convert.ToDateTime(r[(int)ClaimColumns.LastSuccessFulPaymentData].ToString()),
                                                                          EffectivePeriod = model.EffectivePeriod,
                                                                          Validated = true,
                                                                          IsValid = true,
                                                                          DateCreated = DateTime.Now,
                                                                          CreatedBy = user.Email
                                                                      }; 
                                        db.MemberClaim.AddObject(memberClaim);
                                        db.SaveChanges();
                                        #endregion
                                    }
                                }
                            }

                        #endregion
                    }
                    #endregion
                    #endregion
                    break;
                case "Members":
                    #region Members
                    #region Validate Structure
                    if (dr["MemberNumber"].ToString() != "MemberNumber")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [MemberNumber] in" + key
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    #endregion

                    #endregion
                    break;
                case "Employer":
                    if (dr["MemberNumber"].ToString() != "MemberNumber")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [MemberNumber] in" + key
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    break;
                case "Transaction":
                    if (dr["MemberNumber"].ToString() != "MemberNumber")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [MemberNumber] in" + key
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    break;
                case "Payment":
                    if (dr["MemberNumber"].ToString() != "MemberNumber")
                    {
                        ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), "Sheet Column [MemberNumber] in" + key
                                                + " not in file", (int)ErrorHelper.ErrorTokin.FileStructureError);
                    }
                    break;
            }

            #region Additional Validation
            try
            {
                List<Map> map = new List<Map>();
                using (Data db = new Data())//use the database context to allow automatic cleaning of the connection to the database
                {
                    map = db.Map.Where(i => i.sheetName == key).Select(x => x).ToList();//Get the map list to allow the loading of the question 
                }
                

                foreach (Map mapItem in map)//Loop through the items in the maping table
                {
                    Question question;
                    using (Data db = new Data())//use the database context to allow automatic cleaning of the connection to the database
                        {
                            question = db.Question.Where(i => i.QuestionId == mapItem.questionId).Select(x => x).SingleOrDefault();//Get the question object 
                        }
                        if (question.AnswerFromList)//check if the answer comes from the database
                        {
                            FundAnswerSheet comparisonRecord;
                            using (Data db = new Data())//use the database context to allow automatic cleaning of the connection to the database
                            {
                                comparisonRecord =
                                        db.FundAnswerSheet
                                            .Where(i => i.FundId == model.FundId && i.QuestionId == question.QuestionId)
                                            .Select(x => x)
                                            .SingleOrDefault();//Get the comparison record that is used to validate the record against the database
                            }
                             
                            #region Looping the teable to evaluate
                            for (int rec = 1; rec < table.Rows.Count - 1; rec++)
                            {
                                using (Data db = new Data())
                                    //use the database context to allow automatic cleaning of the connection to the database
                                {
                                #region Switch 

                                switch (question.AnswerDataTypeId)
                                {
                                    case 1: //Boolean
                                        try
                                        {
                                            bool boolQuestion =
                                                Convert.ToBoolean(table.Rows[rec][(int) mapItem.map1].ToString().Trim());
                                                //try convert the item in the record set to the needed data type
                                            bool boolAnswer =
                                                Convert.ToBoolean(comparisonRecord.AnswerValue.ToString().Trim());
                                                //try convert the item from the database to the required data type
                                            if (boolQuestion != boolAnswer) //Evaluate the question and answer
                                                ExceptionHelper.CreateException(
                                                    Convert.ToInt32(HttpContext.Current.Session[FileLog]),question.Question1 +  " is false", mapItem.mapCatagoryId);
                                                    //Write the exception into the database
                                        }
                                        catch (System.Exception ex) //Catch the exception
                                        {
                                            ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, 99);
                                                //Write the exception into the database
                                        }
                                        break;
                                    case 2: //String
                                        try
                                        {
                                            string stringQuestion =
                                                table.Rows[rec][(int) mapItem.map1].ToString().Trim();
                                                //try convert the item in the record set to the needed data type
                                            string stringAnswer = comparisonRecord.AnswerValue.ToString();
                                                //try convert the item from the database to the required data type
                                            if (stringQuestion != stringAnswer) //Evaluate the question and answer
                                                ExceptionHelper.CreateException(
                                                    Convert.ToInt32(HttpContext.Current.Session[FileLog]), question.Question1 + " is false", mapItem.mapCatagoryId);
                                                    //Write the exception into the database
                                        }
                                        catch (System.Exception ex) //Catch the exception
                                        {
                                            ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, 99);
                                                //Write the exception into the database
                                        }
                                        break;
                                    case 3: //Integer
                                        try
                                        {
                                            int integerQuestion =
                                                Convert.ToInt32(table.Rows[rec][(int) mapItem.map1].ToString().Trim());
                                                //try convert the item in the record set to the needed data type
                                            int interAnswer = Convert.ToInt32(comparisonRecord.AnswerValue);
                                                //try convert the item from the database to the required data type
                                            if (interAnswer != integerQuestion) //Evaluate the question and answer
                                                ExceptionHelper.CreateException(
                                                    Convert.ToInt32(HttpContext.Current.Session[FileLog]), question.Question1 + " is false", mapItem.mapCatagoryId);
                                                    //Write the exception into the database
                                        }
                                        catch (System.Exception ex) //Catch the exception
                                        {
                                            ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, 99);
                                                //Write the exception into the database
                                        }
                                        break;
                                    case 4: //Numberic
                                        try
                                        {
                                            decimal decimalQuestion =
                                                Convert.ToDecimal(table.Rows[rec][(int) mapItem.map1].ToString().Trim());
                                                //try convert the item in the record set to the needed data type
                                            decimal decimalAnswer = Convert.ToDecimal(comparisonRecord.AnswerValue);
                                                //try convert the item from the database to the required data type
                                            if (decimalQuestion != decimalAnswer) //Evaluate the question and answer
                                                ExceptionHelper.CreateException(
                                                    Convert.ToInt32(HttpContext.Current.Session[FileLog]), question.Question1 + " is false", mapItem.mapCatagoryId);
                                                    //Write the exception into the database
                                        }
                                        catch (System.Exception ex) //Catch the exception
                                        {
                                            ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, 99);
                                                //Write the exception into the database
                                        }
                                        break;
                                    case 5: //Count
                                        try
                                        {
                                            int questionCount =
                                                table.Rows[rec][(int) mapItem.map1].ToString().Trim().Count();
                                                //Trim the record and then count the characters 
                                            int answerCount = Convert.ToInt32(comparisonRecord.AnswerValue);
                                                //convert answer to a int to compare to the question
                                            if (questionCount != answerCount) //Evaluate the question and answer
                                                ExceptionHelper.CreateException(
                                                    Convert.ToInt32(HttpContext.Current.Session[FileLog]), question.Question1 + " is false", mapItem.mapCatagoryId);
                                                    //Write the exception into the database

                                        }
                                        catch (System.Exception ex) //Catch the exception
                                        {
                                            ExceptionHelper.CreateException(
                                                Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message, 99);
                                                //Write the exception into the database
                                        }
                                        break;
                                }

                                #endregion
                                }
                        }
                            #endregion
                        }
                        else //Load the old file and compare
                            // the map will give you the column that needs to be compared
                            //loop through the rows of the table with the map for the column to compare
                            //add two next data types Count and compare
                        {

                            FundAnswerSheet comparisonRecord;
                            using (Data db = new Data())
                            {
                                comparisonRecord =
                                        db.FundAnswerSheet
                                            .Where(i => i.FundId == model.FundId && i.QuestionId == question.QuestionId)
                                            .Select(x => x)
                                            .SingleOrDefault();//Get the comparison record that is used to validate the record against the database
                            }
                           DataSet old = (DataSet)HttpContext.Current.Session[SessionPerviouseSet];
                            DataTable currentOldTable = old.Tables[key];
                            for (int rec = 1; rec < table.Rows.Count - 1; rec++)
                            {
                                string newRecord = table.Rows[rec][(int) mapItem.map1].ToString().Trim();//when looping through the old sheet dataSet get the record that needs to be evaluated
                                var query = from d in currentOldTable.AsEnumerable()
                                            where d.Field<string>((int) mapItem.map1).Contains(newRecord)
                                            select d;
                                
                                string oldRecord = currentOldTable.Rows[rec][(int) mapItem.map1].ToString().Trim();//get the old record
                                using (Data db = new Data())
                                {
                                    if (newRecord != oldRecord) //evalutate
                                    {
                                        ExceptionHelper.CreateException(
                                            Convert.ToInt32(HttpContext.Current.Session[FileLog]),
                                            db.Exception.Where(
                                                i =>
                                                i.QuestionId == question.QuestionId &&
                                                i.SheetId == mapItem.mapCatagoryId).Select(x => x).Single().
                                                ExceptionDescription, 99); //Write the exception into the database
                                    }
                                }
                            }
                        }
                    }
                
            }
            catch (System.Exception ex)
            {
                ExceptionHelper.CreateException(Convert.ToInt32(HttpContext.Current.Session[FileLog]), ex.Message,99);//Write the exception into the database
            }
            #endregion
        }

        private static bool CheckForPreviouseVersion(FileModel newFile)
        {
            int fundId = newFile.FundId;
            string filename = newFile.FileName;
            using (Data db = new Data())
            {
                
                var q = db.FileLog.Where(i => i.FundId == fundId && i.FileName == filename).Select(x => x);
                if(q != null)
                {
                    if(q.Count() > 0)
                    {
                        var res =
                         db.FileLog.Where(i => i.FundId == fundId && i.FileName == filename).OrderByDescending(
                        i => i.DateCreated).Select(x => x.FileData).First();
                        if (res != null)
                        {
                            byte[] buffer = res;
                            MemoryStream mem = new MemoryStream(buffer, 0, buffer.Count());
                            //mem.Read();
                            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(mem);//Load the file into the Excel Data Reader
                            DataSet result = excelReader.AsDataSet();//Send the results to DataSet
                            HttpContext.Current.Session[SessionPerviouseSet] = result;
                            return true;
                        }
                    }
                }
                return false;
            }

        }


        private void CreateClaimRecord()
        {
        }
    }
}